package prac.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.json.JSONArray;
import org.json.JSONObject;

import prac.forms.PracForm;

import com.amkor.commons.util.db.MultiDBConnection;
import db.Constant;


public class PracDAO {
	

	String ps = "sencha.personal";
	
public String draw_personal(){
	
	String jsonResult = "";
	Connection m_conn = null;
	PreparedStatement m_psmt = null;
	ResultSet m_rs = null;
	JSONArray jsonArray = new JSONArray();
	
	try{
		m_conn = MultiDBConnection.getConnection(Constant.getMYSQL());
		String Query = "SELECT * FROM "+ps;
		
		m_psmt = m_conn.prepareStatement(Query);
		m_rs = m_psmt.executeQuery();
		JSONObject j = null;
		
		if(m_rs != null){
			while(m_rs.next()){
				j = new JSONObject();
				
				
				j.put("firstname", 		m_rs.getString(1));
				j.put("lastname", 		m_rs.getString(2));
				j.put("company", 		m_rs.getString(3));
				j.put("email",		 	m_rs.getString(4));
				j.put("dob", 			m_rs.getString(5));
				j.put("age", 			m_rs.getInt(6));
				j.put("coworker", 		m_rs.getString(7));
				
				jsonArray.put(j);
				
			}
		}
		
		if(jsonArray != null && jsonArray.length() >0){
			j = new JSONObject();
			j.put("success", true);
			j.put("div_data", jsonArray);
			jsonResult = j.toString();
		}
		
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		MultiDBConnection.cleanup(m_rs, m_psmt, m_conn);
	}
	return jsonResult;
}

public void insert_personal(PracForm pForm){
	
	Connection m_conn = null;
	PreparedStatement m_psmt = null;
	ResultSet m_rs = null;
	int i = 0;
	
	try{
		m_conn = MultiDBConnection.getConnection(Constant.getMYSQL());
		String Query = "INSERT INTO "+ps+" SET  firstName=?, lastName=?, company=?, email=?, dob=?, age=?, coworker=?, crt_dt=now()";
		
		m_psmt = m_conn.prepareStatement(Query);
		
		m_psmt.setString(++i, pForm.getFirstName());
		m_psmt.setString(++i, pForm.getLastName());
		m_psmt.setString(++i, pForm.getCompany());
		m_psmt.setString(++i, pForm.getEmail());
		m_psmt.setString(++i, pForm.getDob());
		m_psmt.setInt(++i, pForm.getAge());
		m_psmt.setString(++i, pForm.getCoworker());
		
		m_psmt.executeUpdate();
		
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		MultiDBConnection.cleanup(m_rs, m_psmt, m_conn);
	}
}
	
	
}
